#!/usr/bin/perl -w

#####################################################################
# Program Variables - Go see the central config.pm!
#####################################################################
use FindBin qw($Bin);
use lib "$Bin";
use config;
use layout;

#####################################################################
# Program Modules
#####################################################################
# CGI Modules
use CGI;
use CGI::Carp;
use CGI::Pretty;

# Database Modules
use DBI;

# Data::Dumper for debugging
#use Data::Dumper;

# POSIX Stuff...
use POSIX;

#####################################################################
# Program Start
#####################################################################
my $cgi = new CGI;

my $pagetitle = &read_config_option("tank_name") . " Water Statistics";

# Web Page output
&ts_pagestart($pagetitle);

# Parameter List Checking
my %params = $cgi->Vars;

foreach $key (keys %params) {

    if ($key eq "display_history") {
        &view_full_history();
    } elsif ($key eq "Add Results") {
        &write_results(%params);
    } elsif ($key =~ /delete_(.*)/i) {
        &delete_result($1);
    }
}

#######################################
# Get the most recent statistics

my @tablerows;

my $dbh = DBI->connect("dbi:SQLite:dbname=$database_file", "", "");

push(@tablerows, $cgi->th([ "Statistic", "Last Collected", "Last Result", "New Result" ]));

# Collate the most recent data.
my $test_sth = $dbh->prepare("SELECT * FROM tests ORDER BY displayorder ASC");
$test_sth->execute();
while (my $testref = $test_sth->fetchrow_hashref()) {
    my %testhash = %{$testref};
    my $name     = $testhash{name};
    my $title    = $testhash{title};

    # Pull the previous data out of the database
    my $dataref = $dbh->selectrow_hashref("SELECT * FROM aquarium WHERE name=? ORDER BY collected DESC LIMIT 1", undef, $name);
    if (defined($dataref)) {
        my %datahash = %{$dataref};

        # Check to see if value has a multiplier
        if ((defined($testhash{multiplier})) and ($testhash{multiplier} ne "")) {

            # If so, if the multiplier is less than one, we are doing a "value remaining" calculation
            if ($testhash{multiplier} < 0) {
                push(
                    @tablerows,
                    $cgi->td([
                            $title,
                            $datahash{collected},
                            $datahash{value} . " ("
                              . (1 + (floor(($datahash{value} / $testhash{multiplier}) * 100) / 100))
                              . " $testhash{multipliertitle})"
                        ])
                      . $cgi->td({ -class => "left" }, [ $cgi->textfield(-name => $name, -size => "5") ]));

                # Otherwise we are doing a normal calculation
            } else {
                push(
                    @tablerows,
                    $cgi->td([
                            $title,
                            $datahash{collected},
                            $datahash{value} . " (" . (floor(($datahash{value} / $testhash{multiplier}) * 100) / 100) . " $testhash{multipliertitle})"
                        ])
                      . $cgi->td({ -class => "left" }, [ $cgi->textfield(-name => $name, -size => "5") ]));
            }

            # And for non-multiplier values
        } else {
            push(@tablerows,
                    $cgi->td([ $title, $datahash{collected}, $datahash{value} ])
                  . $cgi->td({ -class => "left" }, [ $cgi->textfield(-name => $name, -size => "5") ]));
        }
    } else {
        push(@tablerows, $cgi->td([ $title, "-", "-" ]) . $cgi->td({ -class => "left" }, [ $cgi->textfield(-name => $name, -size => "5") ]));
    }
}

my (undef, $min, $hour, $mday, $mon, $year, undef, undef, undef) = localtime(time);

$mon  += 1;       # This gives us the month in the range of 1-12.
$year += 1900;    # This gives us the four-digit year

push(
    @tablerows,
    $cgi->td({ -class => "right", colspan => "3" }, ["Collected At (Year-Month-Day 24hr:time)"]) . $cgi->td(
        { -class => "left" }, [
                $cgi->popup_menu(-name => "col_year", -default => $year, -values => [ 2012 .. 2020 ]) . "-"
              . $cgi->popup_menu(-name => "col_mon",  -default => $mon,  -values => [ 1 .. 12 ]) . "-"
              . $cgi->popup_menu(-name => "col_mday", -default => $mday, -values => [ 1 .. 31 ]) . " at "
              . $cgi->popup_menu(-name => "col_hour", -default => $hour, -values => [ 0 .. 23 ]) . ":"
              . $cgi->popup_menu(-name => "col_min",  -default => $min,  -values => [ 0 .. 59 ])

        ]));
push(
    @tablerows,
    $cgi->td(
        { colspan => "4" },
        $cgi->submit(-name => "display_history", -label => "Show Last 24 Hours") . " | " . $cgi->submit(-name => "Add Results")));

# Close the database
$test_sth->finish;
$dbh->disconnect;
#######################################

# Show the extra text for this graph as defined in the config file
print $cgi->center($cgi->p(&read_config_option("stats_message")));

# Show an overall stats graph, not very useful, but somewhat pretty
print $cgi->p($cgi->center($cgi->img({ -src => "graphs/statistics.png" })));

# Start the forms
print $cgi->start_form;
print $cgi->start_table({ -align => 'center' });
print $cgi->Tr([@tablerows]);
print $cgi->end_table;
print $cgi->end_form;
print $cgi->p($cgi->center("Return to the " . $cgi->a({ -href => "./" }, &read_config_option("tank_name") . " Index") . " Page."));
print $cgi->end_html;
print "\n";
exit 0;

#####################################################################
# Program Subroutines
#####################################################################
sub view_full_history {
    my $prevday = `date "+%Y-%m-%d %H:%M:%S" --date="-1 day"`;
    my $dbh = DBI->connect("dbi:SQLite:dbname=$database_file", "", "");
    my @sampletimes =
      @{ $dbh->selectcol_arrayref("SELECT DISTINCT collected FROM aquarium WHERE collected >= ? ORDER BY collected DESC", undef, $prevday) };
    my $record_sth = $dbh->prepare_cached("SELECT collected,name,value FROM aquarium WHERE collected=? AND name=?");

    my @pageheaders;
    push(@pageheaders, "Collected");

    my $test_sth = $dbh->prepare("SELECT * FROM tests ORDER BY displayorder ASC");

    $test_sth->execute();
    while (my $testref = $test_sth->fetchrow_hashref()) {
        my %testhash = %{$testref};
        push(@pageheaders, $testhash{title});
    }

    print $cgi->start_form;
    print $cgi->start_table({ -align => 'center', -width => "95%" });

    print $cgi->Tr([ $cgi->th([ @pageheaders, "&nbsp;" ]) ]);

    foreach $time (@sampletimes) {
        my @sampleline;
        push(@sampleline, $time);

        my $test_sth = $dbh->prepare("SELECT * FROM tests ORDER BY displayorder ASC");
        $test_sth->execute();
        while (my $testref = $test_sth->fetchrow_hashref()) {
            my %testhash = %{$testref};
            my $name     = $testhash{name};

            $record_sth->execute($time, $name);
            my $sample_ref = $record_sth->fetchrow_hashref();
            if (defined($sample_ref)) {
                my %datahash = %{$sample_ref};

                # Check to see if value has a multiplier
                if ((defined($testhash{multiplier})) and ($testhash{multiplier} ne "")) {

                    # If so, if the multiplier is less than one, we are doing a "value remaining" calculation
                    if ($testhash{multiplier} < 0) {
                        push(@sampleline,
                                $datahash{value} . " ("
                              . (1 + (floor(($datahash{value} / $testhash{multiplier}) * 100) / 100))
                              . " $testhash{multipliertitle})");

                        # Otherwise we are doing a normal calculation
                    } else {
                        push(@sampleline,
                                $datahash{value} . " ("
                              . (floor(($datahash{value} / $testhash{multiplier}) * 100) / 100)
                              . " $testhash{multipliertitle})");
                    }

                    # And for non-multiplier values
                } else {
                    push(@sampleline, $datahash{value});
                }
            } else {
                push(@sampleline, "&nbsp;");
            }
        }

        push(@sampleline, $cgi->submit(-name => "delete_$time", -label => "Delete Entry"));
        print $cgi->Tr([ $cgi->td([@sampleline]) ]);
    }

    print $cgi->end_table;
    print $cgi->end_form;
    print $cgi->center($cgi->p("Return to the " . $cgi->a({ -href => "tankstats.cgi" }, $pagetitle) . " Page."));
    print $cgi->end_html;
    print "\n";

    # Cleanup DB connections
    $test_sth->finish;
    undef $test_sth;
    $record_sth->finish;
    undef $record_sth;

    # Disconnect from DB
    $dbh->disconnect;

    exit 0;
}

sub write_results {
    my %cgiparams = @_;

    my $dbh = DBI->connect("dbi:SQLite:dbname=$database_file", "", "");
    my $sth = $dbh->prepare_cached("INSERT INTO aquarium (collected, name, value) VALUES (datetime(?), ?, ?)");

    my $time = sprintf "%04.4d-%02.2d-%02.2d %02.2d:%02.2d", $cgiparams{col_year}, $cgiparams{col_mon}, $cgiparams{col_mday}, $cgiparams{col_hour},
      $cgiparams{col_min};
    $time ||= strftime "%Y-%m-%d %H:%M:%S", localtime;

    my $test_sth = $dbh->prepare("SELECT * FROM tests ORDER BY displayorder ASC");
    $test_sth->execute();
    while (my $testref = $test_sth->fetchrow_hashref()) {
        my %testhash = %{$testref};
        my $result   = $testhash{name};

        if (defined($cgiparams{$result})) {
            if ($cgiparams{$result} ne "") {

                # Check to see if the value has a multiplier
                if (defined($testhash{multiplier})) {

                    # If so, then check to see if we where given an uncalculated value.
                    if (($cgiparams{$result} =~ /(.*)d/i) or ($cgiparams{$result} =~ /(.*)r/i)) {
                        my $basevalue = $1;
                        print $cgi->comment("Was uncalculated value");

                        if ($testhash{multiplier} < 0) {
                            $sth->execute($time, $result, ((1 - $basevalue) * abs($testhash{multiplier})));
                        } else {
                            $sth->execute($time, $result, ($basevalue * $testhash{multiplier}));
                        }
                    } else {
                        $sth->execute($time, $result, $cgiparams{$result});
                    }
                } else {
                    $sth->execute($time, $result, $cgiparams{$result});
                }

                print $cgi->comment("Wrote result entry for $result taken $time"), "\n";
            }
        }
    }

    # Cleanup DB connections
    $test_sth->finish;
    undef $test_sth;

    # Close the database
    $dbh->disconnect;

    print $cgi->comment("Data Written"), "\n";

    $cgi->delete_all();

    return;
}

sub delete_result {
    my $entrytime = shift @_;

    # Open the database
    my $dbh = DBI->connect("dbi:SQLite:dbname=$database_file", "", "");

    # Delete the record
    $dbh->do("DELETE FROM aquarium WHERE collected=datetime(?)", undef, $entrytime);

    # Close the database
    $dbh->disconnect;

    print $cgi->comment("Entry Deleted"), "\n";

    $cgi->delete_all();

    return;
}

